from backend.api.connection import POOL
# from connection import POOL   # 单独运行用这个
import datetime


# 新增计划
# 返回
# {'pid': 0
#  'ok': 1,
#  'msg':''}
def addPlan(uid, bid, everyday_num):
    conn = POOL.connection()
    cur = conn.cursor()
    response = {'pid': 0, 'ok': 0, 'msg': ''}
    plan_start_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    # 找pid
    pid = 1
    sql = "SELECT MAX(pid) FROM planlist"
    cur.execute(sql)
    result = cur.fetchall()
    # print(result)
    if result[0][0] is not None:
        pid = result[0][0] + 1
    # print(pid)

    # 判断用户是否已有词书的计划
    sql = "SELECT COUNT(pid) FROM planlist WHERE bid = {} AND uid = {}".format(bid, uid)
    cur.execute(sql)
    result = cur.fetchall()
    if result[0][0] != 0:
        response['msg'] = '该计划已存在'
        return response
        
    # 找bookname, total_num
    sql = "SELECT bookname,total_num FROM booklist WHERE bid = {}".format(bid)
    cur.execute(sql)
    result = cur.fetchall()
    bookname = result[0][0]
    total_num = result[0][1]

    # 向计划管理表中插入新数据
    sql = "INSERT INTO planlist(" \
          "pid, bid, bookname, uid, create_time, " \
          "edit_time, total_num, remain_num, everyday_num,day_remain_num) " \
          "VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

    cur.execute(sql, (pid, bid, bookname, uid,
                      plan_start_time, plan_start_time,
                      total_num, total_num, everyday_num, everyday_num))

    # 创建用户的计划表
    sql = "CREATE TABLE IF NOT EXISTS `zhifou_word`.`plan_%d`(" \
          "`word` varchar(255) NOT NULL, " \
          "`learn_times` int(10) NULL, " \
          "`last_time` timestamp NULL, " \
          "`ignore` int(1) NULL, " \
          "PRIMARY KEY (`word`))" % (pid)

    cur.execute(sql)
    conn.commit()

    # 初始化用户计划表
    sql = "SELECT word FROM book_{}".format(bid)
    cur.execute(sql)
    word_list = cur.fetchall()

    sql = "INSERT INTO plan_{}(word) VALUE(%s)".format(pid)
    cur.executemany(sql, word_list)
    conn.commit()

    sql = "UPDATE plan_{} SET `learn_times` = 0, `ignore` = 0".format(pid)
    cur.execute(sql)
    conn.commit()
    cur.close()

    response['pid'] = pid
    response['ok'] = 1
    return response


# 修改计划
# 返回
# {
#  'ok': 1,
#  'msg':''}
def updatePlan(pid, everyday_num):
    conn = POOL.connection()
    cur = conn.cursor()
    response = {'ok': 0, 'msg': ''}
    edit_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    # 获取已经背过的词数
    sql = "SELECT everyday_num, day_remain_num FROM planlist WHERE pid = %s" % (str(pid))
    cur.execute(sql)
    result = cur.fetchall()
    recited_num = result[0][0]-result[0][1]
    day_remain_num = int(everyday_num) - recited_num

    sql = "UPDATE planlist SET everyday_num = %s,day_remain_num = %s" \
          " WHERE pid = %s" % (everyday_num, day_remain_num, pid)
    cur.execute(sql)
    conn.commit()
    cur.close()
    response['ok'] = 1
    # print(response)
    return response


# 获取计划列表    //每次登陆界面都需要发送请求，以便在新的一天更新day_remain_num
# 返回
# {'data': [{pid:111,bookname:"CET6",create_time:2000-01-01 00:00:00,
#            edit_time:2000-01-01 00:00:00,total_num:2333,remain_num,everyday_num:30}……
#           ],
#  'ok': 1,
#  'msg':''}
def getPlanList(uid):
    conn = POOL.connection()
    cur = conn.cursor()
    response = {'data': [], 'ok': 0, 'msg': ''}
    now_time = datetime.datetime.now().strftime("%Y-%m-%d")


    sql = 'SELECT pid, bookname, create_time, edit_time, ' \
          'total_num, remain_num, everyday_num, day_remain_num FROM planlist WHERE uid = {}'.format(uid)
    cur.execute(sql)
    resultset = cur.fetchall()
    data = []
    for result in resultset:
        plan = {'pid':0, 'bookname':"", 'create_time':"", 'edit_time':"",
                'total_num':0, 'remain_num':0, 'everyday_num':0, 'day_remain_num':0}
        plan['pid'] = result[0]
        plan['bookname'] = result[1]
        plan['create_time'] = datetime.datetime.strftime(result[2],'%Y-%m-%d')
        plan['edit_time'] = datetime.datetime.strftime(result[3],'%Y-%m-%d')
        plan['total_num'] = result[4]
        plan['remain_num'] = result[5]
        plan['everyday_num'] = result[6]

        # 如果最近执行时间不是今天，更新day_remain_num
        if plan['edit_time'] == now_time:
            plan['day_remain_num'] = result[7]
        else:
            plan['day_remain_num'] = result[6]
            sql = 'UPDATE planlist SET day_remain_num = everyday_num ' \
                  'WHERE pid = {}'.format(result[0])
            cur.execute(sql)
            conn.commit()
        data.append(plan)

    cur.close()
    response['data'] = data
    response['ok'] = 1
    # print(response)
    return response


# 加载学习计划 句子没有进行处理
# 返回
# {'data': [{'word':"abbreviation",'translation':"n.节略，缩写，缩短",'sentence':"……"},……
#           ],
#  'ok': 1,
#  'msg':''}
def loadPlan(pid):
    conn = POOL.connection()
    cur = conn.cursor()
    response = {'data': [], 'ok': 0, 'msg': ''}
    data = []
    now_time = datetime.datetime.now().strftime("%Y-%m-%d")
    # 更新计划管理表中 edit_time
    sql = 'UPDATE planlist SET edit_time = {} ' \
          'WHERE pid = {}'.format("{1}{0}{1}".format(now_time,"'"),pid)
    cur.execute(sql)
    conn.commit()

    # 查找需要下载的量,对应的词书表         此处没有考虑计划完成后另学的情况
    sql = 'SELECT day_remain_num,bid FROM planlist WHERE pid = {}'.format(pid)
    cur.execute(sql)
    res = cur.fetchall()
    download_num = res[0][0]
    bid = res[0][1]

    # 优先选择不会的
    sql = 'SELECT t1.word,t2.translation,t2.sentence ' \
          'FROM plan_{} t1,book_{} t2 ' \
          'WHERE t1.word = t2.word AND t1.ignore = 1 LIMIT {}'.format(pid, bid, download_num)
    cur.execute(sql)
    resultset = cur.fetchall()
    for result in resultset:
        word_data = {'word':"", 'translation':"", 'sentence':""}
        word_data['word'] = result[0]
        word_data['translation'] = result[1]
        word_data['sentence'] = purifySentence(str(result[2]))
        data.append(word_data)
    download_num -= len(resultset)

    # 剩下随机选没学习过的词
    sql = 'SELECT t1.word,t2.translation,t2.sentence ' \
          'FROM plan_{} t1,book_{} t2 ' \
          'WHERE t1.word = t2.word AND t1.learn_times = 0 ORDER BY rand() LIMIT {}'.format(pid, bid, download_num)
    cur.execute(sql)
    resultset = cur.fetchall()

    for result in resultset:
        word_data = {'word':"", 'translation':"", 'sentence':""}
        word_data['word'] = result[0]
        word_data['translation'] = result[1]
        word_data['sentence'] = purifySentence(str(result[2]))
        data.append(word_data)

    sql = 'UPDATE userinfo SET last_planid = {} ' \
          'WHERE uid = (SELECT uid FROM planlist WHERE pid = {})'.format(pid, pid)
    cur.execute(sql)
    conn.commit()

    cur.close()
    response['data'] = data
    response['ok'] = 1
    print(response)
    return response

# def selectsen():
#     conn = POOL.connection()
#     cur = conn.cursor()
#     sql = 'SELECT sentence ' \
#           'FROM book_{}' \
#           ' ORDER BY rand() LIMIT {}'.format(30, 2)
#     cur.execute(sql)
#     resultset = cur.fetchall()
#     for result in resultset:
#         print(result[0])
#         purifySentence(str(result[0]))

# 加载复习（当天学过的单词）
# 返回
# {'data': [{'word':"abbreviation",'translation':"n.节略，缩写，缩短",'sentence':"……"},……
#           ],
#  'ok': 1,
#  'msg':''}
def loadReview(pid):
    conn = POOL.connection()
    cur = conn.cursor()
    response = {'data': [], 'ok': 0, 'msg': ''}
    data = []
    now_day = datetime.datetime.now().strftime("%Y%m%d")

    # 查找需要下载的量,对应的词书表         此处没有考虑计划完成后另学的情况
    sql = 'SELECT everyday_num,bid FROM planlist WHERE pid = {}'.format(pid)
    cur.execute(sql)
    res = cur.fetchall()
    download_num = res[0][0]
    bid = res[0][1]

    sql = 'SELECT t1.word,t2.translation ' \
          'FROM plan_{} t1,book_{} t2 ' \
          'WHERE t1.word = t2.word AND DATE_FORMAT(t1.last_time,"%Y%m%d") = {}'.format(pid, bid, now_day)
    cur.execute(sql)

    resultset = cur.fetchall()
    for result in resultset:
        word_data = {'word': "", 'translation': ""}
        word_data['word'] = result[0]
        word_data['translation'] = result[1]
        data.append(word_data)
    # 数量不足再从已学过的中选取
    if len(data) < download_num:
        sql = 'SELECT t1.word,t2.translation ' \
            'FROM plan_{} t1,book_{} t2 ' \
            'WHERE t1.word = t2.word AND DATE_FORMAT(t1.last_time,"%Y%m%d") != {} ' \
            'AND t1.learn_times > 0 ORDER BY rand() LIMIT {}'.format(pid, bid, now_day, str(download_num - len(data)))
        cur.execute(sql)
        resultset = cur.fetchall()
        for result in resultset:
            word_data = {'word': "", 'translation': ""}
            word_data['word'] = result[0]
            word_data['translation'] = result[1]
            data.append(word_data)
    cur.close()
    response['data'] = data
    response['ok'] = 1
    return response

# 获取计划详细信息
def getPlanInfo(pid):
    conn = POOL.connection()
    cur = conn.cursor()
    response = {'data': [], 'ok': 0, 'msg': ''}
    sql = '''SELECT pid,bookname,create_time,
        edit_time,total_num,remain_num,everyday_num,
        day_remain_num FROM planlist WHERE pid = {}'''.format(pid)
    cur.execute(sql)
    res = cur.fetchone()
    plan = {'pid': 0, 'bookname': "", 'create_time': "", 'edit_time': "",
            'total_num': 0, 'remain_num': 0, 'everyday_num': 0, 'day_remain_num': 0}
    plan['pid'] = res[0]
    plan['bookname'] = res[1]
    plan['create_time'] = datetime.datetime.strftime(res[2], '%Y-%m-%d')
    plan['edit_time'] = datetime.datetime.strftime(res[3], '%Y-%m-%d')
    plan['total_num'] = res[4]
    plan['remain_num'] = res[5]
    plan['everyday_num'] = res[6]
    plan['day_remain_num'] = res[7]
    response['data'] = [plan, ]
    cur.close()
    response['ok'] = 1
    return response

# 净化例句文本
def purifySentence(sentence):
    if sentence == 'None':
        return ''
    splitsen = sentence.split(r'/r/n')
    count = 0
    result = ''
    for item in splitsen:
        item = item.lstrip().rstrip()
        if len(item) < 2:
            continue
        if count == 0:
            result += item
        else:
            sign = '$$' if count % 2 == 0 else '&&'
            result += sign + item
        count += 1
    return result


if __name__ == '__main__':
    #  addPlan(1, 1, 30)
    #  updatePlan(1, 20)
    #  getPlanList(1)
    #  loadPlan(1)
    # purifySentence(s)
    # selectsen()
    updatePlan(1,20)
    pass
